﻿using System;
using System.Collections.Generic;
using System.Text;
using Directory.Model;
using System.Data;
using System.Data.SqlClient;

namespace Directory.DAL
{
	public class MessageBoard:Directory.IDAL.IMessgeBoard
	{
		string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString;

		#region IMessgeBoard 成员

		MessageInfo Directory.IDAL.IMessgeBoard.CreateMessage(string title, string author, string content, string authorIp, DateTime releaseDate, bool isChecked, int topState)
		{
			MessageInfo msg = null;

			SqlConnection conn = new SqlConnection(connstring);
			SqlCommand comm = new SqlCommand("dr_MessageBoard_Insert", conn);
			comm.CommandType = CommandType.StoredProcedure;

			comm.Parameters.Add("@MessageId", SqlDbType.Int);
			comm.Parameters["@MessageId"].Direction = ParameterDirection.Output;
			comm.CommandType = CommandType.StoredProcedure;
			comm.Parameters.Add("@Title", SqlDbType.NVarChar, 128).Value = title;
			comm.Parameters.Add("@Author", SqlDbType.NVarChar, 128).Value = author;
			comm.Parameters.Add("@Message", SqlDbType.NText).Value = content;
			comm.Parameters.Add("@AuthorIP", SqlDbType.NChar, 30).Value = authorIp;
			comm.Parameters.Add("@ReleaseDate", SqlDbType.DateTime).Value = releaseDate;
			comm.Parameters.Add("@IsChecked", SqlDbType.Bit).Value = isChecked;
			comm.Parameters.Add("@TopState", SqlDbType.Int).Value = topState;

			conn.Open();

			try
			{
				comm.ExecuteNonQuery();

				SqlCommand cmd = new SqlCommand("dr_MessageBoard_GetMessageInfo", conn);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add("@MessageId",SqlDbType.Int).Value=int.Parse(comm.Parameters["@MessageId"].Value.ToString());
				using (SqlDataReader dr = cmd.ExecuteReader())
				{
					if (dr.Read())
					{
						msg = new MessageInfo(
							dr.GetInt32(0),
							DALHelper.GetNullableString(dr, 1),
							DALHelper.GetNullableString(dr, 2),
							DALHelper.GetNullableString(dr, 3),
							DALHelper.GetNullableString(dr, 4),
							dr.GetDateTime(5),
							DALHelper.GetNullableString(dr, 6),
							DALHelper.GetNullableString(dr, 7),
							dr.GetDateTime(8),
							dr.GetBoolean(9),
							dr.GetInt32(10));
					}
				}
			}
			finally
			{
				conn.Close();
			}

			return msg;
		}

		void Directory.IDAL.IMessgeBoard.ReplyMessage(int MessageId, string checker, string reply, DateTime replyDate, bool isChecked, int topState)
		{
			SqlConnection conn = new SqlConnection(connstring);

			SqlCommand comm = new SqlCommand("dr_MessageBoard_Reply", conn);
			comm.CommandType = CommandType.StoredProcedure;
			comm.CommandType = CommandType.StoredProcedure;
			comm.Parameters.Add("@MessageID", SqlDbType.Int).Value = MessageId;
			comm.Parameters.Add("@Checker", SqlDbType.NVarChar, 128).Value = checker;
			comm.Parameters.Add("@Reply", SqlDbType.NText).Value = reply;
			comm.Parameters.Add("@ReplyDate", SqlDbType.DateTime).Value = replyDate;
			comm.Parameters.Add("@IsChecked", SqlDbType.Bit).Value = isChecked;
			comm.Parameters.Add("@TopState", SqlDbType.Int).Value = topState;

			conn.Open();
			try
			{
				comm.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		void Directory.IDAL.IMessgeBoard.DeleteMessage(int msgID)
		{
			SqlConnection conn = new SqlConnection(connstring);
			SqlCommand comm = new SqlCommand("dr_MessageBoard_delete", conn);
			comm.CommandType = CommandType.StoredProcedure;

			comm.Parameters.Add("@MessageID", SqlDbType.Int).Value = msgID;
			conn.Open();
			try
			{
				comm.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}

		IList<MessageInfo> Directory.IDAL.IMessgeBoard.GetMessageList(bool isChecked,int pageIndex, int pageSize, out int totalRecords)
		{
			IList<MessageInfo> msgList = new List<MessageInfo>();

			SqlConnection conn = new SqlConnection(connstring);
			SqlCommand comm = new SqlCommand("dr_MessageBoard_GetMessages", conn);
			comm.CommandType = CommandType.StoredProcedure;

			comm.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
			comm.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
			comm.Parameters.Add("@IsChecked", SqlDbType.Bit).Value = isChecked;
			comm.Parameters.Add("@TotalRecords", SqlDbType.Int);
			comm.Parameters["@TotalRecords"].Direction = ParameterDirection.Output;

			conn.Open();
			try
			{
				using (SqlDataReader dr = comm.ExecuteReader())
				{
					while (dr.Read())
					{
						MessageInfo msg = new MessageInfo(
							dr.GetInt32(0),
							DALHelper.GetNullableString(dr, 1),
							DALHelper.GetNullableString(dr, 2),
							DALHelper.GetNullableString(dr, 3),
							DALHelper.GetNullableString(dr, 4),
							dr.GetDateTime(5),
							DALHelper.GetNullableString(dr, 6),
							DALHelper.GetNullableString(dr, 7),
							dr.GetDateTime(8),
							dr.GetBoolean(9),
							dr.GetInt32(10));
						msgList.Add(msg);
					}
				}
				totalRecords = int.Parse(comm.Parameters["@TotalRecords"].Value.ToString());
			}
			finally
			{
				conn.Close();
			}
			return msgList;
		}

		MessageInfo Directory.IDAL.IMessgeBoard.GetMessage(int msgId)
		{
			MessageInfo msg = null;
			SqlConnection conn = new SqlConnection(connstring);

			SqlCommand cmd = new SqlCommand("dr_MessageBoard_GetMessageInfo", conn);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("@MessageId", SqlDbType.Int).Value = msgId;

			conn.Open();

			try
			{
				using (SqlDataReader dr = cmd.ExecuteReader())
				{
					if (dr.Read())
					{
						msg = new MessageInfo(
							dr.GetInt32(0),
							DALHelper.GetNullableString(dr, 1),
							DALHelper.GetNullableString(dr, 2),
							DALHelper.GetNullableString(dr, 3),
							DALHelper.GetNullableString(dr, 4),
							dr.GetDateTime(5),
							DALHelper.GetNullableString(dr, 6),
							DALHelper.GetNullableString(dr, 7),
							dr.GetDateTime(8),
							dr.GetBoolean(9),
							dr.GetInt32(10));
					}
				}
			}
			finally
			{
				conn.Close();
			}
			return msg;
		}

		#endregion
	}
}
